package com.study.manage.dal;

import java.util.Date;
import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;

import com.study.manage.domain.Blog;
import com.study.manage.domain.EvaluateApply;
import com.study.manage.domain.enumm.EAState;
import com.study.manage.domain.enumm.EvaluateApplyVerifyState;
import com.study.manage.domain.enumm.EvaluateVerify;

public interface EvaluateApplyRepository extends PagingAndSortingRepository<EvaluateApply, Long>,
        JpaSpecificationExecutor<EvaluateApply> {

    @Query("select count(t) from EvaluateApply t,User u where t.state!=1 and t.evaluateId=:evaluateId and t.userId=u.id and (to_days(t.createdAt)=to_days(:date) or :date is null)")
    int countByEvaluateIdAndDay(@Param("evaluateId") Long evaluateId, @Param("date") Date date);

    @Query("select count(t) from EvaluateApply t where t.state!=1 and t.evaluateId=:evaluateId "
            + "and (t.verify=:pass or :pass is null) "
            + "and (t.verifyState=:verifyState or :verifyState is null) ")
    int countByIdAndVerifyAndVerifyState(@Param("evaluateId") Long evaluateId,
            @Param("pass") EvaluateVerify pass,
            @Param("verifyState") EvaluateApplyVerifyState verifyState);

    @Query("select count(t.id) from EvaluateApply t where t.state!=1 and t.evaluateId in (:evaluateIds) and (to_days(createdAt)=to_days(:date) or :date is null) and (t.referenceId=:referenceId or :referenceId is null)")
    int getEvaluateIdApplyCount(@Param("evaluateIds") List<Long> evaluateIds,
            @Param("date") Date date, @Param("referenceId") Long referenceId);

    @Query("select count(t) from EvaluateApply t where t.state=0 and t.verify=1 and t.evaluateId=:evaluateId and (t.referenceId=:referenceId or :referenceId is null) and (to_days(createdAt)=to_days(:date) or :date is null)")
    int countSuccessByEvaluateId(@Param("evaluateId") Long evaluateId, @Param("date") Date date,
            @Param("referenceId") Long referenceId);

    @Query("select count(t) from EvaluateApply t where t.state!=1 and t.verify=:verify and t.evaluateId=:evaluateId and (to_days(createdAt)=to_days(:date) or :date is null)")
    int countByEvaluateIdAndVerify(@Param("evaluateId") Long evaluateId,
            @Param("verify") EvaluateVerify verify, @Param("date") Date date);

    @Query("select t from EvaluateApply t,User u where t.userId=u.id and t.state!=1 and t.evaluateId=:evaluateId "
            + "and (u.name like %:name% or :name is null) and (t.verifyState=:verifyState or :verifyState is null) order by t.createdAt desc")
    Page<EvaluateApply> findByEvaluateIdAndState(@Param("evaluateId") Long evaluateId,
            @Param("name") String name, @Param("verifyState") EvaluateApplyVerifyState verifyState,
            Pageable pageable);

    @Query("select b from EvaluateApply t,User u,EvaluateApplyWork eaw, Blog b where t.userId=u.id and eaw.evaluateApplyId=t.id and eaw.blogId=b.id and t.state!=1 and t.evaluateId=:evaluateId "
            + "and (to_days(b.createdAt)=to_days(:date) or :date is null) and (u.name like %:name% or :name is null) and (t.verifyState=:verifyState or :verifyState is null) order by eaw.createdAt desc")
    Page<Blog> findByEvaluateIdAndStateWork(@Param("evaluateId") Long evaluateId,
            @Param("name") String name, @Param("verifyState") EvaluateApplyVerifyState verifyState,
            @Param("date") Date date, Pageable pageable);

    @Query("select b from EvaluateApply t,User u,EvaluateApplyWork eaw, Blog b where t.userId=u.id and eaw.evaluateApplyId=t.id and eaw.blogId=b.id and t.state!=1and t.id=:id ")
    Blog findByAppalyEvaluateId(@Param("id") Long id);

    @Query("select b from EvaluateApply t,User u,EvaluateApplyWorkVote eawv,EvaluateApplyWork eaw,Blog b where t.userId=u.id and eaw.evaluateApplyId=t.id "
            + "and eaw.id=eawv.evaluateApplyWorkId and eaw.blogId=b.id and eawv.blogId=b.id and t.state!=1 and eawv.state=0 and eaw.state=0 and b.state=0 and t.evaluateId=:evaluateId "
            + "and (to_days(b.createdAt)=to_days(:date) or :date is null) and (u.name like %:name% or :name is null) and (t.verifyState=:verifyState or :verifyState is null) group by b.id order by count(b.id) desc")
    Page<Blog> findByEvaluateIdAndStateTp(@Param("evaluateId") Long evaluateId,
            @Param("name") String name, @Param("verifyState") EvaluateApplyVerifyState verifyState,
            @Param("date") Date date, Pageable pageable);

    @Query("select b from EvaluateApply t,User u,EvaluateApplyWorkVote eawv,EvaluateApplyWork eaw,Blog b where t.userId=u.id and eaw.evaluateApplyId=t.id "
            + "and eaw.id=eawv.evaluateApplyWorkId and eaw.blogId=b.id and eawv.blogId=b.id and t.state!=1 and eawv.state=0 and eaw.state=0 and b.state=0 and t.evaluateId=:evaluateId "
            + "and (to_days(b.createdAt)=to_days(:date) or :date is null) and (u.name like %:name% or :name is null) and (t.verifyState=:verifyState or :verifyState is null) group by b order by LENGTH(b.extUrl) desc,b.extUrl desc")
    Page<Blog> findByEvaluateIdAndStatePF(@Param("evaluateId") Long evaluateId,
            @Param("name") String name, @Param("verifyState") EvaluateApplyVerifyState verifyState,
            @Param("date") Date date, Pageable pageable);

    @Query("select t from EvaluateApply t, EvaluateGroupDetail egd,User u "
            + "where t.evaluateId=egd.evaluateId and t.userId=u.id and (u.name like %:name% or :name is null)"
            + "and egd.groupId=:egId and (egd.id=:egdId or :egdId is null) and t.state!=1")
    Page<EvaluateApply> findByEgId(@Param("egId") Long egId, @Param("egdId") Long egdId,
            @Param("name") String name, Pageable pageable);

    @Query("select t from EvaluateApply t, EvaluateGroupDetail egd,User u "
            + "where t.evaluateId=egd.evaluateId and t.userId=u.id "
            + "and egd.groupId=:egId and t.state!=1")
    List<EvaluateApply> findByEgId(@Param("egId") Long egId);

    @Query(value = "select egd.label,DATE_FORMAT(t.created_at,'%Y-%m-%d'),count(t.user_id) from sa_evaluate_apply as t "
            + "left join sa_evaluate_group_detail as egd on t.evaluate_id=egd.evaluate_id "
            + "where egd.group_id=:egId and t.state!=1 "
            + "group by egd.id,to_days(t.created_at) order by t.created_at asc", nativeQuery = true)
    List<Object> findByEgIdGroupByEgdId(@Param("egId") Long egId);

    @Query("select t.createdAt,count(t.id) from EvaluateApply t  where t.state!=1 and t.evaluateId in (:evaluateIds) "
            + "and to_days(t.createdAt)>=to_days(:beginAt) and to_days(t.createdAt)<=to_days(:endAt) group by to_days(t.createdAt)")
    List<Object[]> countByEvaluateIdInGroupByDay(@Param("beginAt") Date beginAt,
            @Param("endAt") Date endAt, @Param("evaluateIds") List<Long> evaluateIds);

    @Query(nativeQuery = true,
            value = "select t.created_at,count(t.id) as count,'报名人数（提交）' as type from sa_evaluate_apply t  where t.state in (0,2,3) and t.evaluate_id in (:evaluateIds) "
                    + "and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at) "

                    + " UNION ALL select t.created_at,count(t.id),'报名过审人数' as type from sa_evaluate_apply t  where t.state!=1 and t.verify=1 and t.evaluate_id in (:evaluateIds) "
                    + "and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at)"

                    + " UNION ALL select t.created_at,count(t.id),'报名成功人数' as type from sa_evaluate_apply t  where t.state=0 and t.verify=1 and t.evaluate_id in (:evaluateIds) "
                    + "and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at)"

                    + " UNION ALL select t.created_at,count(t.id),'已拒绝' as type from sa_evaluate_apply t  where t.verify=2 and t.evaluate_id in (:evaluateIds) "
                    + "and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at)"

                    + " UNION ALL select t.created_at,count(t.id),'待审核' as type from sa_evaluate_apply t  where t.state!=1 and t.verify=0 and t.evaluate_id in (:evaluateIds) "
                    + "and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at)"

                    + " UNION ALL select t.created_at,sum(t.total),'报名收费' as type from sa_orders t  where t.typee=1 and t.evaluate_id in (:evaluateIds) "
                    + "and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at)")
    List<Object[]> countByEvaluateIdInGroupByDays(@Param("beginAt") Date beginAt,
            @Param("endAt") Date endAt, @Param("evaluateIds") List<Long> evaluateIds);


    @Query(nativeQuery = true,
            value = "select t.created_at,count(t.id) as count,'报名人数（提交）' as type from sa_evaluate_apply t  where t.state in (0,2,3) and t.evaluate_id in (:evaluateIds) and t.reference_id in (:referenceId)"
                    + " and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at) "

                    + " UNION ALL select t.created_at,count(t.id),'报名成功人数' as type from sa_evaluate_apply t  where t.state=0 and t.verify=1 and t.evaluate_id in (:evaluateIds)  and t.reference_id in (:referenceId)"
                    + " and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at)")
    List<Object[]> countByEvaluateIdInAndReferenceIdInGroupByDays(
            @Param("referenceId") List<Long> referenceId, @Param("beginAt") Date beginAt,
            @Param("endAt") Date endAt, @Param("evaluateIds") List<Long> evaluateIds);

    @Query(nativeQuery = true,
            value = "select t.created_at,count(t.id) as count,'报名人数（提交）' as type from sa_evaluate_apply t  where t.state!=1 and t.evaluate_id in (:evaluateIds) and (t.reference_id=:referenceId or :referenceId is null) "
                    + "and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at) "

                    + "UNION ALL select t.created_at,count(t.id),'报名成功人数' as type from sa_evaluate_apply t  where t.state=0 and t.verify=1 and t.evaluate_id in (:evaluateIds) and (t.reference_id=:referenceId or :referenceId is null) "
                    + "and to_days(t.created_at)>=to_days(:beginAt) and to_days(t.created_at)<=to_days(:endAt) group by to_days(t.created_at)")
    List<Object[]> countByEvaluateId(@Param("beginAt") Date beginAt, @Param("endAt") Date endAt,
            @Param("evaluateIds") List<Long> evaluateIds, @Param("referenceId") Long referenceId);

    @Query("select t from EvaluateApply t, User u where t.state!=1 and t.evaluateId=:evaluateId and t.userId=u.id "
            + "and (u.name like %:name% or :name is null) "
            + "and (t.verifyState=:verifyState or :verifyState is null) "
            + "and (t.verify=:pass or :pass is null) " + "and (t.state=:state or :state is null) "
            + "and (to_days(t.createdAt)=to_days(:date) or :date is null) ")
    Page<EvaluateApply> findByEvaluateIdAndDateAndName(@Param("evaluateId") Long evaluateId,
            @Param("date") Date date, @Param("name") String name,
            @Param("verifyState") EvaluateApplyVerifyState verifyState,
            @Param("state") EAState state, @Param("pass") EvaluateVerify pass, Pageable pageable);

    @Query("select t from EvaluateApply t, User u where t.state!=1 and t.evaluateId=:evaluateId and t.userId=u.id "
            + "and (u.name like %:name% or :name is null) "
            + "and (t.verify=:verify or :verify is null) "
            + "and (t.state=:state or :state is null) "
            + "and (t.referenceId=:referenceId or :referenceId is null) "
            + " and (to_days(t.createdAt)>=to_days(:beginAt) or :beginAt is null) "
            + " and (to_days(t.createdAt)<=to_days(:endAt) or :endAt is null) ")
    Page<EvaluateApply> findEvaluateApplys(@Param("evaluateId") Long evaluateId,
            @Param("beginAt") Date beginAt, @Param("endAt") Date endAt, @Param("name") String name,
            @Param("verify") EvaluateVerify verify, @Param("state") EAState state,
            @Param("referenceId") Long referenceId, Pageable pageable);


    @Query("select t from EvaluateApply t, User u where t.state!=1 and t.evaluateId=:evaluateId and t.userId=u.id "
            + "and (u.name like %:name% or :name is null) "
            + "and (t.verify=:verify or :verify is null) "
            + "and (t.state=:state or :state is null) " + " and t.referenceId in (:referenceId)"
            + " and (to_days(t.createdAt)>=to_days(:beginAt) or :beginAt is null) "
            + " and (to_days(t.createdAt)<=to_days(:endAt) or :endAt is null) ")
    Page<EvaluateApply> findEvaluateApplysAndRefIn(@Param("evaluateId") Long evaluateId,
            @Param("beginAt") Date beginAt, @Param("endAt") Date endAt, @Param("name") String name,
            @Param("verify") EvaluateVerify verify, @Param("state") EAState state,
            @Param("referenceId") List<Long> referenceId, Pageable pageable);


    @Query("select t from EvaluateApply t where t.state!=4 and t.evaluateId=:evaluateId  "
            + "and (t.fullName=:name or t.number=:name or t.phone=:name or t.groupName=:name or :name is null) "
            + "and (t.userId=:userId or t.referenceId=:userId or :userId is null) "
            + "and (t.verify=:verify or :verify is null) "
            + "and (t.state=:state or :state is null) "
            + "and (t.referenceId=:referenceId or :referenceId is null) "
            + " and (to_days(t.createdAt)>=to_days(:beginAt) or :beginAt is null) "
            + " and (to_days(t.createdAt)<=to_days(:endAt) or :endAt is null) ")
    Page<EvaluateApply> findEvaluateApplys(@Param("evaluateId") Long evaluateId,
            @Param("beginAt") Date beginAt, @Param("endAt") Date endAt, @Param("name") String name,
            @Param("userId") Long userId, @Param("verify") EvaluateVerify verify,
            @Param("state") EAState state, @Param("referenceId") Long referenceId,
            Pageable pageable);

    @Query("select t from EvaluateApply t, User u where t.state!=1 and t.evaluateId=:evaluateId and t.userId=u.id "
            + "and (t.verify=:pass or :pass is null) "
            + "and (t.referenceId=:referenceId or :referenceId is null) "
            + " and (to_days(t.createdAt)>=to_days(:beginAt) or :beginAt is null) "
            + " and (to_days(t.createdAt)<=to_days(:endAt) or :endAt is null) ")
    List<EvaluateApply> findEvaluateApplys(@Param("evaluateId") Long evaluateId,
            @Param("beginAt") Date beginAt, @Param("endAt") Date endAt,
            @Param("pass") EvaluateVerify pass, @Param("referenceId") Long referenceId);

    @Query("select t from EvaluateApply t where t.evaluateId=:evaluateId and t.userId=:userId and t.verify=1and t.state!=1 ")
    EvaluateApply findByEvaluateIdAndUserId(@Param("evaluateId") Long evaluateId,
            @Param("userId") Long userId);

    @Modifying
    @Query("update EvaluateApply set verifyState=:verifyState,passAt=:date where id=:id")
    void update(@Param("id") Long id, @Param("verifyState") EvaluateApplyVerifyState verifyState,
            @Param("date") Date date);

    @Modifying
    @Query("update EvaluateApply set remark=:remark where id=:id")
    void updateRemark(@Param("id") Long id, @Param("remark") String remark);

    @Modifying
    @Query("update EvaluateApply set verify=?2,state=?3,success_time=?4 where id=?1 ")
    void update(Long id, EvaluateVerify verify, EAState state, Date date);

    @Modifying
    @Query("update EvaluateApply set verify=?2 where id=?1 ")
    void update(Long id, EvaluateVerify verify);

    @Query(nativeQuery = true,
            value = "SELECT Min(created_at),Max(created_at) FROM sa_evaluate_apply where evaluate_id in (?1)")
    List<Object[]> getMinMaxTime(List<Long> evaluateIds);

    @Query("select t from EvaluateApply t where t.state!=4 and t.evaluateId=:evaluateId"
            + " and (t.referenceId=:referenceId or :referenceId is null)"
            + " and (to_days(t.createdAt)>=to_days(:beginAt) or :beginAt is null) "
            + " and (to_days(t.createdAt)<=to_days(:endAt) or :endAt is null)")
    List<EvaluateApply> findByEvaluateIdAndNotDraftAndBeginAtDate(
            @Param("evaluateId") Long evaluateId, @Param("referenceId") Long referenceId,
            @Param("beginAt") Date beginAt, @Param("endAt") Date endAt);

    @Query("select t from EvaluateApply t where t.state!=4 and t.evaluateId=:evaluateId"
            + " and (t.referenceId=:referenceId or :referenceId is null)"
            + " and (to_days(t.createdAt)>=to_days(:beginAt) or :beginAt is null) "
            + " and (to_days(t.createdAt)<=to_days(:endAt) or :endAt is null)")
    Page<EvaluateApply> findByEvaluateIdAndNotDraftAndBeginAtDate(
            @Param("evaluateId") Long evaluateId, @Param("referenceId") Long referenceId,
            @Param("beginAt") Date beginAt, @Param("endAt") Date endAt, Pageable pageable);

    @Query("select t from EvaluateApply t where t.state!=4 and t.evaluateId=:evaluateId"
            + " and (t.referenceId=:referenceId or :referenceId is null)"
            + " and (to_days(t.createdAt)=to_days(:date) or :date is null)")
    Page<EvaluateApply> findByEvaluateIdAndNotDraftAndDay(@Param("evaluateId") Long evaluateId,
            @Param("referenceId") Long referenceId, @Param("date") Date date, Pageable pageable);


    Integer countByEvaluateIdInAndVerifyAndState(List<Long> evaluateIds, EvaluateVerify verify,
            EAState state);

    @Query("select count(t.id) from EvaluateApply t where t.evaluateId in (:evaluateIds) and t.verify=:verify and t.state=:state and (to_days(t.createdAt)=to_days(:date) or :date is null)")
    Integer countByEvaluateIdInAndVerifyAndStateAndDate(
            @Param("evaluateIds") List<Long> evaluateIds, @Param("verify") EvaluateVerify verify,
            @Param("state") EAState state, @Param("date") Date date);

    @Query("select count(t.id) from EvaluateApply t where t.evaluateId in (:evaluateIds)"
            + " and (t.verify in (:verifys)) and (t.state in (:states))"
            + " and (to_days(t.createdAt)=to_days(:date) or :date is null)")
    Integer countByEvaluateIdInAndVerifyInAndStateInAndDate(
            @Param("evaluateIds") List<Long> evaluateIds,
            @Param("verifys") List<EvaluateVerify> verifys, @Param("states") List<EAState> states,
            @Param("date") Date date);


    @Query("select count(t.id) from EvaluateApply t where t.evaluateId in (:evaluateIds) and t.referenceId in (:referenceId)"
            + " and (t.verify in (:verifys)) and (t.state in (:states))"
            + " and (to_days(t.createdAt)=to_days(:date) or :date is null)")
    Integer countByEvaluateIdInAndReferenceIdAndVerifyInAndStateInAndDate(
            @Param("evaluateIds") List<Long> evaluateIds,
            @Param("referenceId") List<Long> referenceId,
            @Param("verifys") List<EvaluateVerify> verifys, @Param("states") List<EAState> states,
            @Param("date") Date date);



    @Query("select count(t.id) from EvaluateApply t where t.evaluateId in (:evaluateIds)"
            + " and (t.verify in (:verifys)) and (t.state in (:states)) and (verifyState=:verifyState)"
            + " and (to_days(t.createdAt)=to_days(:date) or :date is null)")
    Integer countByEvaluateIdInAndVerifyInAndStateInAndVerifyStateAndDate(
            @Param("evaluateIds") List<Long> evaluateIds,
            @Param("verifys") List<EvaluateVerify> verifys, @Param("states") List<EAState> states,
            @Param("verifyState") EvaluateApplyVerifyState verifyState, @Param("date") Date date);

}
